
package keter.service.tx;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * <p>
 * 数据库直接操作工具
 * </p>
 * 
 * @author 顾力行 - gulx@neusoft.com
 * @version 1.0 Created on Apr 15, 2009 6:48:04 PM
 */
public class DBUtilForKeterTx {
	/**
	 * Logger for this class
	 */
	private static final Logger log = LoggerFactory.getLogger(DBUtilForKeterTx.class);
	private static Connection conn;
	private static Statement stmt;
	private static ResultSet rstr;
	private static String strConnectionURL = null;

	/**
	 * 配置文件初始化：只做一次！
	 */
	static {
		log.info(" init datasource context...");
		strConnectionURL = "jdbc:hsqldb:hsql://localhost";
	};

	/**
	 * <p>
	 * 执行查询
	 * </p>
	 * 
	 * @param strConnectionURL
	 * @param userNamePwd
	 * @param sql
	 * @param type
	 * @return
	 * @author: 顾力行 - gulx@neusoft.com
	 * @date: Created on Apr 27, 2009 10:44:07 AM
	 */
	public static ResultSet executeQuery(String sql) {
		prepareDBDriver();
		prepareQuery(sql);
		return rstr;
	}

	public static void executeUpdate(String sql) {
		prepareDBDriver();
		prepareUpdate(sql);
	}

	/**
	 * <p>
	 * Method ：prepare
	 * <p>
	 * Description : 构建数据库驱动
	 * 
	 * @author 顾力行-gulx@neusoft.com
	 *         <p>
	 *         --------------------------------------------------------------<br>
	 *         修改履历：<br>
	 *         <li>2010-8-30，gulx@neusoft.com，创建方法；<br>
	 *         --------------------------------------------------------------
	 *         <br>
	 *         </p>
	 */
	private static void prepareDBDriver() {
		try {
			Class.forName("org.hsqldb.jdbcDriver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	/**
	 * <p>
	 * 结果集准备
	 * </p>
	 * 
	 * @param strConnectionURL
	 * @param userNamePwd
	 * @param sql
	 * @throws SQLException
	 * @author: 顾力行 - gulx@neusoft.com
	 * @date: Created on Apr 20, 2009 4:55:34 PM
	 */
	private static void prepareQuery(String sql) {
		try {
			conn = DriverManager.getConnection(strConnectionURL, "sa", "");
			stmt = conn.createStatement();
			rstr = stmt.executeQuery(sql);
		} catch (SQLException e) {
			e.printStackTrace();
			close();
		}
	}

	/**
	 * <p>
	 * 结果集准备
	 * </p>
	 * 
	 * @param strConnectionURL
	 * @param userNamePwd
	 * @param sql
	 * @throws SQLException
	 * @author: 顾力行 - gulx@neusoft.com
	 * @date: Created on Apr 20, 2009 4:55:34 PM
	 */
	private static void prepareUpdate(String sql) {
		try {
			conn = DriverManager.getConnection(strConnectionURL, "sa", "");
			stmt = conn.createStatement();
			stmt.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
			close();
		}
	}

	/**
	 * <p>
	 * 关闭连接 需要在结果集使用完成之后操作
	 * </p>
	 * 
	 * @author: 顾力行 - gulx@neusoft.com
	 * @date: Created on Apr 20, 2009 4:57:13 PM
	 */
	public static void close() {
		if (conn != null && stmt != null) {
			try {
				conn.close();
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public static void main(String args[]) throws SQLException {
		String sql = "select count(1) from user;";
		ResultSet rstr = DBUtilForKeterTx.executeQuery(sql);
		while (rstr.next()) {
			String USR_HOST = rstr.getString(1);
			System.out.println("round1: " + USR_HOST);
		}
		DBUtilForKeterTx.close();
	}
}